# -*- coding: utf-8 -*- 
"""
Project: automation-api-project
Creator: jinsheng
Create time: 2021-11-09 19:49
IDE: PyCharm
Introduction: MySql操作相关
"""

import pymysql

from common.readConfig import WRConfigFile


class OperateMysql:
    """
    操作mysql数据库，包含初始化，增删改查，提交，关闭，回滚等操作
    """

    def __init__(self, version=17, autocommit=False):
        conf = WRConfigFile().read_conf
        if version == 17:
            self.conn = pymysql.connect(host=conf('database', 'host_17'), port=int(conf('database', 'port_17')),
                                        user=conf('database', 'user_17'), password=conf('database', 'password_17'),
                                        database=conf('database', 'base_name_17'),
                                        autocommit=autocommit)
        if version == 221:
            self.conn = pymysql.connect(host=conf('database', 'host_221'), port=int(conf('database', 'port_221')),
                                        user=conf('database', 'user_221'), password=conf('database', 'password_221'),
                                        database=conf('database', 'base_name_221'),
                                        autocommit=autocommit)
        if version == 201:
            self.conn = pymysql.connect(host=conf('database', 'host_201'), port=int(conf('database', 'port_201')),
                                        user=conf('database', 'user_201'), password=conf('database', 'password_201'),
                                        database=conf('database', 'base_name_201'),
                                        autocommit=autocommit)
        if version == '221_n':
            self.conn = pymysql.connect(host=conf('database', 'host_221_n'), port=int(conf('database', 'port_221_n')),
                                        user=conf('database', 'user_221_n'), password=conf('database', 'password_221_n'),
                                        database=conf('database', 'base_name_221_n'),
                                        autocommit=autocommit)
        if version == 'dxy':
            self.conn = pymysql.connect(host=conf('database', 'host_dxy'), port=int(conf('database', 'port_dxy')),
                                        user=conf('database', 'user_dxy'), password=conf('database', 'password_dxy'),
                                        database=conf('database', 'base_name_dxy'),
                                        autocommit=autocommit)
        self.cur = self.conn.cursor()

    def commit(self):
        # 提交事务
        try:
            self.conn.commit()
        except Exception:
            print('提交失败！')

    def close(self):
        # 关闭游标，关闭链接
        try:
            self.cur.close()
            self.conn.close()
        except Exception:
            print('关闭数据库失败')

    def rollback(self):
        # 回滚
        try:
            self.conn.rollback()
        except Exception:
            print('回滚失败')

    @staticmethod
    def dict_2_str_and(dictin):
        """
        将字典变成，key='value' and key='value'的形式
        """
        tmplist = []
        for k, v in dictin.items():
            tmp = "%s='%s'" % (str(k), (str(v)))
            tmplist.append(' ' + tmp + ' ')
        return ' and '.join(tmplist)

    @staticmethod
    def dict_2_str(dictin):
        """
        将字典变成，key='value',key='value' 的形式
        """
        tmplist = []
        for k, v in dictin.items():
            tmp = "%s='%s'" % (str(k), (str(v)))
            tmplist.append(' ' + tmp + ' ')
        return ','.join(tmplist)

    def search_data(self, table_name, conditions=None):
        """
        查询操作
        :param table_name: 表名
        :param conditions: 筛选条件，字典形式传入
        :return: 查询到的数据（元组）
        """
        tmp = self.dict_2_str_and(conditions)
        if conditions:
            sql = f"""select * from {table_name} where """ + tmp  # 按条件查
        else:
            sql = f"""select * from {table_name}"""  # 查整张表
        try:
            self.cur.execute(sql)
        except Exception as e:
            print(e)
        finally:
            res = self.cur.fetchall()
            # self.close()
        return res

    def insert_data(self, table_name, values):
        """
        插入操作
        :param table_name: 表名
        :param values: 插入的内容（字符串）
        :return: None
        """
        sql = f"insert into {table_name} values " + "(" + values + ")"
        try:
            self.cur.execute(sql)
            self.commit()
        except Exception as e:
            self.rollback()
            print(e)
        finally:
            # self.close()
            pass

    def update_data(self, table_name, values, conditions):
        """
        更新操作
        :param table_name: 表名
        :param values: 需要更新的字段（字典）
        :param conditions: 查询筛选条件（字典）
        :return: None
        """
        tmp = self.dict_2_str(values)
        tmp2 = self.dict_2_str_and(conditions)
        sql = f"""update {table_name} set {tmp} where {tmp2}"""
        try:
            self.cur.execute(sql)
            self.commit()
        except Exception as e:
            print(e)
            self.rollback()
        finally:
            pass

    def delete_data(self, table_name, condition=None):
        """
        删除操作
        :param table_name: 表名
        :param condition: 删除的where条件，如果为空，则删除该表所有数据
        :return: None
        """
        tmp = self.dict_2_str_and(condition)
        if condition:
            sql = f"""delete from {table_name} where {tmp}"""
        else:
            sql = f"""delete * from {table_name}"""
        try:
            self.cur.execute(sql)
            self.commit()
        except Exception as e:
            print(e)
            self.rollback()
        finally:
            # self.close()
            pass

    def excute_sql(self, sql):
        """
        执行一条sql语句
        :param sql: 需要执行的sql语句
        :return: 如果是查询语句，返回结果
        """
        try:
            self.cur.execute(sql)
            res = self.cur.fetchall()
            self.commit()
        except Exception as e:
            print(e)
            self.rollback()
        finally:
            self.close()
        return res

    def batch_excute(self, sql):
        """
        批量操作sql语句，一般用于造数据
        @param sql: 需要执行的sql语句
        @return: None
        """
        try:
            self.cur.execute(sql)
        except Exception as e:
            print(e)
            self.rollback()

    def excute_many(self, sql, args_list):
        """
        批量操作数据库升级版
        @param sql: 需要执行的sql语句，其中变量以%s占位
        @param args_list: 变量组成的元组形式列表，例如[(1,2,),(3,4)]
        @return: None
        """
        try:
            self.cur.executemany(sql, args_list)
        except Exception:
            print('出错了，即将回滚')
            self.rollback()


if __name__ == '__main__':
    db = OperateMysql()
    for i in range(10):
        device_id = 'test_per_' + str(i)
        analog_name = 'analog_' + str(i)
        sql = f"""insert into device_center.device_analog_rela values ('',{device_id},{analog_name},)"""
